from urllib.request import urlopen
from urllib.request import Request as request
from urllib import parse
from bs4 import BeautifulSoup as bs
import pymysql.cursors

"""
从学生体质健康网获取学校ID和学校名称
信息包含省/直辖市编码名称、地市编码及名称、区县编码及名称、学校编码及名称
http://www.csh.moe.gov.cn/MOETC/mdepartmentExtAction!toSearchWdOuter.action
2020 新地址 http://www.csh.moe.gov.cn/MOETC/login/loginAction!getAllSchool.action?item=2
"""


class Province:
    def __init__(self, code, name, url):
        self.code = code
        self.name = name
        self.url = url


"""
定义域名
"""
host = "www.csh.moe.gov.cn"
"""
网站主URL
"""
root_path = "http://%s/MOETC/" % host

user_agent = "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.62 Safari/537.36"


def get_schools_by_county(province_id, province_name, city_id, city_name, district_id, district_name, referer):
    form_data = parse.urlencode([
        ("provinceId", province_id),
        ("cityId", city_id),
        ("countyId", district_id),
        ("pageSize", 10000),
        ("pageIndex", 1)
    ])

    # 创建请求对象
    req = request("%slogin/loginAction!getAllSchool.action" % root_path)
    req.add_header("Host", host)
    req.add_header("Origin", "http://%s" % host)
    req.add_header("User-Agent", user_agent)
    req.add_header("Referer", referer)
    # 获得服务器返回的数据
    response = urlopen(req, data=form_data.encode("utf-8"))
    # 处理数据
    page = response.read().decode("utf-8")
    soup = bs(page, "html.parser")

    for child in soup.select("table.h_list-table > tr"):
        if child.select_one("td.h_tdListCenter") is not None and child.select_one("td.h_tdListCenter1") is not None:
            school_name = child.select_one("td.h_tdListCenter").string.strip()
            school_id = child.select_one("td.h_tdListCenter1").string.strip()
            province_id = province_id[0:2]
            city_id = city_id[0:4]
            district_id = district_id[0:6]
            print(
                "province_id = %s, province_name = %s, city_id = %s, city_name = %s, district_id = %s, district_name = %s,school_id = %s ,school_name = %s" %
                (province_id, province_name, city_id, city_name, district_id, district_name, school_id, school_name))
            # 获取数据连接
            connection = pymysql.connect(host="127.0.0.1", user="root", password="root", db="common", charset="utf8mb4")
            try:
                # 获取会话指针
                with connection.cursor() as cursor:
                    # 创建sql语句
                    sql = "replace into `school_info` (`school_id`,`school_name`,`province_id`,`province_name`,`city_id`,`city_name`,`district_id`,`district_name`) values(%s,%s,%s,%s,%s,%s,%s,%s)"
                    # 执行sql语句
                    cursor.execute(sql, (
                        school_id, school_name, province_id, province_name, city_id, city_name, district_id,
                        district_name))
                    # 提交
                    connection.commit()
            finally:
                connection.close()


def get_counties_by_city(province_id, province_name, city_id, city_name, url):
    """
    点击地市获取区县一级列表
    :param province_id:
    :param province_name:
    :param city_id:
    :param city_name:
    :param url:
    :return:
    """
    # 创建请求对象
    req = request(url)
    req.add_header("Host", "www.ip3366.net")
    req.add_header("Origin", root_path)
    req.add_header("User-Agent", user_agent)
    # 获得服务器返回的数据
    response = urlopen(req)
    # 处理数据
    page = response.read().decode("utf-8")
    soup = bs(page, "html.parser")
    i = 0
    for child in soup.select("div.h_select_line"):
        if i == 2:
            school_list_child = child.select("div.h_select_line_right > a")
            if len(school_list_child) > 0:
                for school_child in school_list_child:
                    get_schools_by_county(province_id, province_name, city_id, city_name,
                                          school_child.get("href").strip()[-12:],
                                          school_child.string.strip(), url)
            else:
                get_schools_by_county(province_id, province_name, city_id, city_name, city_id, None, url)
        i += 1


def get_cities_by_province(province_id, province_name, province_url):
    """
    点击省份获取地市一级列表
    :param province_id: 省级ID
    :param province_name: 省名称
    :param province_url: url
    :return: 无
    """
    if province_url is None:
        province_url = "%slogin/loginAction!getAllSchool.action?provinceId=%s&countyId=%s" % (
            root_path, province_id, province_id)
    # 创建请求对象
    req = request(province_url)
    req.add_header("Host", host)
    req.add_header("Origin", root_path)
    req.add_header("User-Agent", user_agent)
    # 获得服务器返回的数据
    response = urlopen(req)
    # 处理数据
    page = response.read().decode("utf-8")
    soup = bs(page, "html.parser")
    i = 0
    for child in soup.select("div.h_select_line"):
        if i == 1:
            city_list_child = child.select("div.h_select_line_right > a")
            for city_child in city_list_child:
                get_counties_by_city(province_id, province_name, city_child.get("href").strip()[-12:],
                                     city_child.string.strip(),
                                     root_path + city_child.get("href").strip())
        i += 1


def get_all(start_province_id):
    if start_province_id is None:
        start_province_id = 100000000000
    elif start_province_id < 100000000000:
        start_province_id = start_province_id * 10000000000
    # 现在全国所有省市的学校名单
    url = "%slogin/loginAction!getAllSchool.action" % root_path
    # 创建请求对象
    req = request(url)
    req.add_header("Host", host)
    req.add_header("Origin", root_path)
    req.add_header("User-Agent", user_agent)
    # 获得服务器返回的数据
    response = urlopen(req)
    # 处理数据
    page = response.read().decode("utf-8")
    soup = bs(page, "html.parser")
    i = 0
    for child in soup.select("div.h_select_line"):
        if i == 0:
            for province_child in child.select("div.h_select_line_right > a"):
                province_id = int(province_child.get("href").strip()[-12:])
                if province_id >= start_province_id:
                    get_cities_by_province(province_child.get("href").strip()[-12:], province_child.string.strip(),
                                           root_path + province_child.get("href").strip())
        i += 1


get_all(66)
